
          /*-------ProductDao By Raj Vallamsetla------

			For the insertion of Base Product

			Method : saveProcessStepProduct(int processStepID,String partNumber,int baseProductID)
			This Method is for saving the ProcessStep in Product table..
			Here partNumber is for Fab-FabName and Sort-SortName respectively for all the steps
			and the baseProductID is saved as parent product Id
          */

package com.scalar.chiptrack.operations.process.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Set;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.HashMap;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.struts.util.LabelValueBean;

import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.ScalarException;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.process.ProcessFlow;
import com.scalar.chiptrack.operations.process.Product;
import com.scalar.chiptrack.operations.process.VendorLocation;
import com.scalar.chiptrack.operations.process.forms.FabProcessSplitForm;
import com.scalar.chiptrack.operations.process.forms.AssemblyProcessSplitForm;
import com.scalar.chiptrack.operations.process.forms.FinishedGoodsForm;
import com.scalar.chiptrack.operations.process.FinishedGoods;
import com.scalar.chiptrack.operations.vendor.VendorAddress;


public class ProductDao implements Constants
{

	public ProductDao(){ }

    // Balaram, on April 17, 2005
    public static Product loadProductInfoByProcessStepId( int processStepId ) throws ScalarException
    {
        Connection conn 		= null;
		PreparedStatement pstmt = null;
		ResultSet rs 			= null;

        Product product = new Product();

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( "select part_number, p_comments from product where processStep_id = ?" );
            pstmt.setInt( 1, processStepId );

			rs = pstmt.executeQuery();

			if( rs.next() )
			{
                product.setPartNumber( rs.getString( "part_number" ) );
			    product.setDescription ( rs.getString( "p_comments" ) );
			}
		}
		catch( Exception e )
		{
			throw new ScalarException ("Could not get the part information from the database for process step ID: " + processStepId );
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

        return product;
    }



    // Balaram, on June 30, 2003
    public static void updateProductDescription( DBTransaction dbTransaction, int processStepId, String description ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean status = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.product.updateProductDescription" ) );

            pstmt.setString( 1, description );
            pstmt.setInt( 2, processStepId );

            pstmt.executeUpdate();

            status = true;
        }
        catch(Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException( "Could not update product description for processStepId: " + processStepId, sql );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null)
            {
                try
                {
                    if (conn != null)
                    {
                        if (status == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null)
            {
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null)
            {
                try{ conn.close(); }catch(SQLException se){}
            }
        }
    }


    // Added by Balaram on Dec 08, 2002
    public static ArrayList getForwardProcessStepInfoByPartNumber (String partNumber) throws ScalarException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try{
            conn = DataSource.getConnection();
            ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.getForwardProcessStepInfoByPartNumber"));
            ps.setString (1, partNumber);
            rs = ps.executeQuery();
            ProcessFlow processFlow = null;

            while (rs.next()){
                processFlow = new ProcessFlow (rs.getInt ("ProcessStep_ID"), rs.getString ("PS_Type"), rs.getString ("Process_Type_Name"));
                list.add (processFlow);
            }

            //System.out.println (list);
        }

        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException ("Could not load process step information for Part Number: " + partNumber, se);
        }

        finally{
            if (rs != null){
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }

    // Added by Balaram on Dec 08, 2002
    public static Set getForwardProcessStepTypeNamesPartNumber (String partNumber) throws ScalarException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        LinkedHashMap StepTypeNameMap = new LinkedHashMap();

        try{
            conn = DataSource.getConnection();
            ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.getForwardProcessStepInfoByPartNumber"));
            ps.setString (1, partNumber);
            rs = ps.executeQuery();

            while (rs.next()) {
                StepTypeNameMap.put (rs.getString ("Process_Type_Name"), "");
            }

            //System.out.println (StepTypeNameMap.keySet());
        }

        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException ("Could not load processStep type names for Part Number: " + partNumber, se);
        }

        finally{
            if (rs != null){
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return StepTypeNameMap.keySet();
    }



    /**
     * returns processStepId, processStepType and processStepName List for give productID
     */
    public static ArrayList getProcessStepInfoByProductId (int productId) throws ScalarException {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try{
            conn = DataSource.getConnection();
            ps = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.getProcessStepInfoByProductId"));
            ps.setInt (1, productId);
            rs = ps.executeQuery();

            while(rs.next()){
                list.add (new org.apache.struts.util.LabelValueBean (rs.getString("PS_Type"), rs.getString("Process_Type_Name") + "|" + rs.getInt ("ProcessStep_ID")));
            }

        }

        catch (SQLException se) {
            se.printStackTrace();
            throw new ScalarException ("Could not load process step information for productID: " + productId, se);
        }

        finally{
            if (rs != null){
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }

    // Balaram, on June 30, 2003
     public static int saveProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID, String mktPartNumber) throws ScalarException{
         return saveProcessStepProduct (dbTransaction, processStepID, partNumber, baseProductID, mktPartNumber, null);
     }

	public static int saveProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID, String mktPartNumber, String comments) throws ScalarException{
		 return saveProcessStepProduct (dbTransaction, processStepID, partNumber, baseProductID, mktPartNumber, null, null, null );
	}

     /*public static int saveProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID, String mktPartNumber, String comments, String isApproved ) throws ScalarException
     {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean status = false;
        int productId = -1;

        try{
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.insertFGProcessStepProduct"));

            pstmt.setInt (1, processStepID);
            pstmt.setString (2, partNumber);
            pstmt.setInt (3, baseProductID);
            pstmt.setString (4, mktPartNumber);
            pstmt.setString (5, comments);
			pstmt.setString (6, isApproved);

            pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;


            pstmt = conn.prepareStatement ("SELECT @@IDENTITY as product_id");
            rset = pstmt.executeQuery();

            if (rset.next() != false) {
                productId =  rset.getInt ("product_id");
            }

            rset.close();
            rset = null;

            status = true;

        }

        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not access VendorLocationList", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

        return productId;
    }*/


	public static int saveProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID, String mktPartNumber, String comments, String isApproved ) throws ScalarException
	{
		return saveProcessStepProduct (dbTransaction, processStepID, partNumber, baseProductID, mktPartNumber, comments, isApproved, null );
	}

	public static int saveProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID, String mktPartNumber, String comments, String isApproved, String productType ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		boolean status = false;
		int productId = -1;

		try
		{
			//Get the connection from regular datasource if its not in a manual transaction
			if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.insertFGProcessStepProduct"));

			pstmt.setInt ( 1, processStepID );
			pstmt.setString ( 2, partNumber );
			pstmt.setInt ( 3, baseProductID );
			pstmt.setString ( 4, mktPartNumber );
			pstmt.setString ( 5, comments );
			pstmt.setString (6, isApproved );
			pstmt.setString (7, productType );
			pstmt.executeUpdate();

			pstmt.close();
			pstmt = null;

			pstmt = conn.prepareStatement ("SELECT @@IDENTITY as product_id");
			rset = pstmt.executeQuery();

			if (rset.next() != false)
			{
				productId =  rset.getInt ("product_id");
			}

			rset.close();
			rset = null;
			status = true;

		}

		catch(Exception sql)
		{
			sql.printStackTrace();
		 	throw new ScalarException("Could not access VendorLocationList", sql);
		}

		finally
		{
			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null) {
				try
				{
					if (conn != null)
					{
						if (status == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if(pstmt != null)
			{
				try{pstmt.close();}catch(SQLException se){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(SQLException se){}
			}
		}
		return productId;
	}


     /**
      * For BinSetup
      * Stores partNumbers and return ProdcutID
      */
    public static int saveProcessStepProduct (DBTransaction dbTransaction, int processStepID, String[] partNumbers, int baseProductID ) throws ScalarException{

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean status = false;
        int productId = -1;

        if ((partNumbers == null) || (partNumbers.length == 0)) {
            return -1;
        }

        try{
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.insertProcessStepProduct"));

            for (int i=0; i<partNumbers.length; i++) {

                pstmt.setInt (1, processStepID);
                pstmt.setString (2, partNumbers[i]);
                pstmt.setInt (3, baseProductID);

                pstmt.addBatch();
            }

            pstmt.executeBatch();
            pstmt.close();
            pstmt = null;


            pstmt = conn.prepareStatement ("SELECT @@IDENTITY as product_id");
            rset = pstmt.executeQuery();

            if (rset.next() != false) {
                productId =  rset.getInt ("product_id");
            }

            rset.close();
            rset = null;

            status = true;
        }
        catch(Exception sql) {
            sql.printStackTrace();
            throw new ScalarException("Could not access VendorLocationList", sql);
        }

        finally{
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (status == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if(pstmt != null){
                try{pstmt.close();}catch(SQLException se){}
            }
            if(conn != null){
                try{ conn.close(); }catch(SQLException se){}
            }
        }

        return productId;
    }


	public static void saveProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID ) throws ScalarException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean status = false;

		try{
                    // Get the connection from regular datasource if its not in a manual transaction
                    if (dbTransaction == null) {
                        conn = DataSource.getConnection();
                    }
                    else {
                        conn = dbTransaction.getConnection();
                    }

                    pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.insertProcessStepProduct"));

                    pstmt.setInt(1,processStepID);
                    pstmt.setString(2,partNumber);
                    pstmt.setInt(3,baseProductID);

                    pstmt.executeUpdate();
                    status = true;

		}

                catch(Exception sql) {
                    sql.printStackTrace();
                    throw new ScalarException("Could not access VendorLocationList", sql);
		}

                finally{
                    // Commit/Rollback here it selef -- if its not in a manual transaction
                    if (dbTransaction == null) {
                        try {
                            if (conn != null) {
                                if (status == true) {
                                    conn.commit();
                                }
                                else {
                                    conn.rollback();
                                }
                            }
                        }
                        catch (SQLException ignore) {}
                    }

                    if(pstmt != null){
                        try{pstmt.close();}catch(SQLException se){}
                    }
                    if(conn != null){
                        try{ conn.close(); }catch(SQLException se){}
                    }
		}
	}

             // Balaram, june 30, 2003
            // Updates partnumber in the product table
            public static void updateProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, int baseProductID ) throws ScalarException
            {
                updateProcessStepProduct (dbTransaction, processStepID, partNumber, null, baseProductID);
            }

            public static void updateProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber, String comments, int baseProductID ) throws ScalarException{
            Connection conn = null;
            PreparedStatement pstmt = null;
            boolean status = false;

            try{
                        // Get the connection from regular datasource if its not in a manual transaction
                        if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
                        }

                        pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.updateProcessStepProduct"));
                        pstmt.setString(1,partNumber);
                        pstmt.setString(2, comments);
                        pstmt.setInt(3,processStepID);
                        pstmt.setInt(4,baseProductID);
                        pstmt.executeUpdate();
                        status = true;

                   }

                    catch(Exception sql) {
                        sql.printStackTrace();
                        throw new ScalarException("Could not access VendorLocationList", sql);
                    }

                    finally{
                        // Commit/Rollback here it selef -- if its not in a manual transaction
                        if (dbTransaction == null) {
                            try {
                                if (conn != null) {
                                    if (status == true) {
                                        conn.commit();
                                    }
                                    else {
                                        conn.rollback();
                                    }
                                }
                            }
                            catch (SQLException ignore) {}
                        }

                        if(pstmt != null){
                            try{pstmt.close();}catch(SQLException se){}
                        }
                        if(conn != null){
                            try{ conn.close(); }catch(SQLException se){}
                        }
                }
            }
		// Ends here
        // added by hareesh on 16th July 2003
        // to update maketing part number in the product table also.
		// Vasanth 08/18/2003 ..begin
		// added the isApproved to update the product whether its approved or not
		// Vasanth 08/18/2003 ..end
        // -- START --
        public static void updateProcessStepProduct (DBTransaction dbTransaction, int processStepID,String partNumber,
                                                     String comments, boolean approvedStatus, int baseProductID, String marketingPartNumber)
                                                     throws ScalarException{
            Connection conn = null;
            PreparedStatement pstmt = null;
            boolean status = false;
			int isApproved = 1;

            try{
                        // Get the connection from regular datasource if its not in a manual transaction
                        if (dbTransaction == null) {
                            conn = DataSource.getConnection();
                        }
                        else {
                            conn = dbTransaction.getConnection();
                        }
						if (approvedStatus==true)
							isApproved = 0;

						pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.updateProcessStepProductAndMktPartNumber"));
                        pstmt.setString(1,partNumber);
                        pstmt.setString(2,comments);
                        pstmt.setString(3,marketingPartNumber);
						pstmt.setInt(4, isApproved);
                        pstmt.setInt(5,processStepID);
                        pstmt.setInt(6,baseProductID);
						pstmt.executeUpdate();
                        status = true;

                   }

                    catch(Exception sql) {
                        sql.printStackTrace();
                        throw new ScalarException("Could not access VendorLocationList", sql);
                    }

                    finally{
                        // Commit/Rollback here it selef -- if its not in a manual transaction
                        if (dbTransaction == null) {
                            try {
                                if (conn != null) {
                                    if (status == true) {
                                        conn.commit();
                                    }
                                    else {
                                        conn.rollback();
                                    }
                                }
                            }
                            catch (SQLException ignore) {}
                        }

                        if(pstmt != null){
                            try{pstmt.close();}catch(SQLException se){}
                        }
                        if(conn != null){
                            try{ conn.close(); }catch(SQLException se){}
                        }
                }
            }
		// -- END --

        public static void updateNextProcessStepId (DBTransaction dbTransaction, int previousStepId, int nextStepId) throws ScalarException {

            Connection conn = null;
            PreparedStatement pstmt = null;
            boolean status = false;

            try{
                // Get the connection from regular datasource if its not in a manual transaction
                if (dbTransaction == null) {
                    conn = DataSource.getConnection();
                }
                else {
                    conn = dbTransaction.getConnection();
                }

                pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.updateNextProcessStepId"));

                pstmt.setInt (1, previousStepId);
                pstmt.setInt (2, nextStepId);

                pstmt.executeUpdate();
                status = true;
            }

            catch(Exception sql) {
                sql.printStackTrace();
                throw new ScalarException ("Could not update nextStepID where processStep ID = " + previousStepId, sql);
            }

            finally{
                // Commit/Rollback here it selef -- if its not in a manual transaction
                if (dbTransaction == null) {
                    try {
                        if (conn != null) {
                            if (status == true) {
                                conn.commit();
                            }
                            else {
                                conn.rollback();
                            }
                        }
                    }
                    catch (SQLException ignore) {}
                }

                if(pstmt != null){
                    try{pstmt.close();}catch(SQLException se){}
                }
                if(conn != null){
                    try{ conn.close(); }catch(SQLException se){}
                }
            }
        }


	// This Method to  get the Part Numbers of respective types by Raj Vallamsetla

	public static ArrayList getPartNumber( String type )  throws ScalarException
	{
    	Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
    	ArrayList partNumberList = null;

	    try
	    {
			partNumberList = new ArrayList();
        	conn = DataSource.getConnection();

        	if( type.equals( "SORT" ) || type.equals( "TEST" ) )
        	{
				pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.sortPartNumber" ) );
			}
			else
			{
		    	pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.fabPartNumber" ) );
			}

        	type = type+"%";
        	pstmt.setString( 1, type );
		    rs =  pstmt.executeQuery();
        	PartNumber partNumber = new PartNumber();

    		while( rs.next() )
	    	{
		    	partNumber = new PartNumber();
			    partNumber.setPartNumber( rs.getString(1) );
				partNumber.setProcessStepId( rs.getString(2) );

				partNumberList.add( partNumber );
			}
		}
        catch( Exception sql )
        {
			sql.printStackTrace();
            throw new ScalarException( "Could not get Part Number by type", sql );
		}
		finally
		{
			if( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore){}
			}
        	if( pstmt != null ) {
            	try { pstmt.close(); } catch (SQLException ignore){}
			}
            if ( conn != null ) {
            	try { conn.close(); } catch (SQLException ignore){}
			}
		}

		return partNumberList;
	}

/*	public static ArrayList getPartNumberBySpecificProcess( String type )  throws ScalarException  {

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;
				 ArrayList partNumberList = null;
		try
		{
			partNumberList = new ArrayList();
			conn = DataSource.getConnection();

			pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.process.getPartNumberBySpecificProcess" ) );
		    pstmt.setString( 1 , type );

			rs =  pstmt.executeQuery();

			ProcessStep partNumber = null;

			while(rs.next())
			{
        		partNumber = new ProcessStep( rs.getInt("ProcessStep_ID"), rs.getString("PS_Type"), rs.getString("Process_Type_Name"), rs.getString("Product_Name"), rs.getInt("Product_ID") );
               	partNumberList.add( partNumber );
			}

		}
    	catch(Exception sql)
		{
			sql.printStackTrace();
            throw new ScalarException ("Could not get Part Number by type",sql);
        }
		finally
		{
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
        }
        if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
        }
	}

	return partNumberList;

 }*/



    public static ArrayList getPartNumberBySpecificProcess(String type)
			throws ScalarException {

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ArrayList partNumberList = null;
		try {
			partNumberList = new ArrayList();
			conn = DataSource.getConnection();

			pstmt = conn.prepareStatement(SqlQueryMappings
					.get("operations.process.getPartNumberBySpecificProcess"));
			pstmt.setString(1, type);

			rs = pstmt.executeQuery();

			PartNumber partNumber = null;

			while (rs.next()) {
				partNumber = new PartNumber();
                partNumber.setProcessStepId(rs.getInt("ProcessStep_ID") +"");
                partNumber.setProcessType(rs.getString("PS_Type"));
                partNumber.setProcessTypeName(rs.getString("PS_Type_Name"));
                partNumber.setPartNumber(rs.getString("Part_Number") );
                partNumber.setProductId(rs.getString("Product_ID") );

                partNumberList.add(partNumber);
			}

		} catch (Exception sql) {
			sql.printStackTrace();
			throw new ScalarException("Could not get Part Number by type", sql);
		}
        finally {
            if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignore) {
				}
			}
            if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException ignore) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ignore) {
				}
			}
		}

		return partNumberList;

	}

 // Method overLoaded to get the partNumber for all the types

public ArrayList getPartNumber() throws ScalarException  {

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;
				 ArrayList partNumberList = null;




		try{

		 partNumberList = new ArrayList();
		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.partNumber"));
		 rs =  pstmt.executeQuery();

		 PartNumber partNumber = new PartNumber();

				while(rs.next())
			{
				partNumber = new PartNumber();
				partNumber.setPartNumber(rs.getString(1));
				partNumber.setProcessStepId(rs.getString(2));
				partNumberList.add(partNumber);
				//System.out.println("partNumber "+rs.getString(1));

			}

		 }

	    	catch(Exception sql) {
                sql.printStackTrace();
                throw new ScalarException ("Could not get Part Number ",sql);
            }


            finally{
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
			}
				return partNumberList;

        }


// Ends here




	public ArrayList getInventoryPartNumber() throws ScalarException  {

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;
				 ArrayList partNumberList = null;




		try{

		 partNumberList = new ArrayList();
		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.inventoryPartNumber"));
		 rs =  pstmt.executeQuery();

		 PartNumber partNumber = new PartNumber();

				while(rs.next())
			{
				partNumber = new PartNumber();
				partNumber.setPartNumber(rs.getString(1));
				partNumber.setProcessStepId(rs.getString(2));
				partNumberList.add(partNumber);

			}

		 }

	    	catch(Exception sql) {
                sql.printStackTrace();
                throw new ScalarException ("Could not get Inventory Part Number ",sql);
            }



            finally{
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
			}

				return partNumberList;

        }

	// Method overloads for the critiria of type By Raj Vallamsetla
	public static ArrayList getInventoryPartNumber(String type) throws ScalarException
	{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt=null;
		ArrayList partNumberList = null;

		try
		{
			partNumberList = new ArrayList();
			conn = DataSource.getConnection();
			if( type.equals( "finished%" ) )
			{
				pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.inventoryMarketingPartNumber" ) );
			}
			else
			{
				pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.process.inventoryPartNumberbyType" ) );
				pstmt.setString( 1, type );
			}
			rs =  pstmt.executeQuery();

		 	PartNumber partNumber = new PartNumber();

			while( rs.next() )
			{
				partNumber = new PartNumber();
				partNumber.setPartNumber( rs.getString(1) );
				partNumber.setProcessStepId( rs.getString(2) );
				partNumberList.add( partNumber );
			}
		}
		catch(Exception sql)
		{
			sql.printStackTrace();
            throw new ScalarException ("Could not get Inventory Part Number ",sql);
		}
        finally
        {
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
		}
		return partNumberList;
    }

	// added by vasanth for retrieving all the boards and kits for board inventory
	public ArrayList getBoardPartNumber() throws ScalarException {

				 Connection conn = null;
				 ResultSet rs    = null;
				 PreparedStatement pstmt=null;
				 ArrayList partNumberList = null;
				 //System.out.println("type in DAO "+type)	;


		try{

		 partNumberList = new ArrayList();
		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.inventoryBoardKitPartNumberbyType"));
		 //pstmt.setString(1,type);
		 rs =  pstmt.executeQuery();
		 //System.out.println("Result set recieved");

		 PartNumber partNumber = new PartNumber();

				while(rs.next())
			{
				partNumber = new PartNumber();
				partNumber.setPartNumber(rs.getString(1));
				partNumber.setProcessStepId(rs.getString(2));
				partNumberList.add(partNumber);

			}

		 }

	    	catch(Exception sql) {
                sql.printStackTrace();
                throw new ScalarException ("Could not get Inventory Part Number ",sql);
            }

             finally{
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
			}

				return partNumberList;

        }




    public static ArrayList getBaseProducts() throws ScalarException
	{
        Connection conn         = null;
		ResultSet rs            = null;
        PreparedStatement pstmt = null;
		ArrayList baseProductList = new ArrayList();

      try
      {

            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.selectBaseProduct"));
            rs = pstmt.executeQuery();
            while(rs.next())
            {

              BaseProduct baseProduct = new BaseProduct();
              baseProduct.setProductId(rs.getString("PRODUCT_ID"));
              baseProduct.setProductName(rs.getString("PART_NUMBER") );
              baseProduct.setObsolete( rs.getInt("Obsolete") == 0 ? true : false);
              baseProductList.add( baseProduct );
            }


      }
      catch(SQLException sql)
      {
			sql.printStackTrace();
			throw new ScalarException("tracking Fab failed ", sql);
		   }

			 finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

			return baseProductList;
}


	public String getStatus(String productId) throws ScalarException
	{
          Connection conn  = null;
		  ResultSet rs     = null;
		  PreparedStatement pstmt = null;
		  String status = "incomplete";

      try{


		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.selectStatus"));
		 pstmt.setString(1,productId);
         rs = pstmt.executeQuery();
		while(rs.next())
		{
		  status = rs.getString(1);

		}

		//System.out.println("Connection Closed");

		   }catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException("In getting the Status of Product ", sql);
		   }

			finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
					//System.out.println(" In get Product Id  "+productId);

			return status;
    }

    public static String getProductType(String productId) throws ScalarException
	{
        Connection conn  = null;
        ResultSet rs     = null;
        PreparedStatement pstmt = null;
        String productType = "";

        try{
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.selectProductType"));
            pstmt.setString(1,productId);
            rs = pstmt.executeQuery();
            while(rs.next())
            {
              productType = rs.getString(1);

            }
        }
        catch(SQLException sql){
            sql.printStackTrace();
            throw new ScalarException("In getting the Type of Product ", sql);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return productType;
    }

  public String getStageOneStatus(String productId) throws ScalarException
	{

		  String status = "";
		  FabProcessSplitDAO fabProcessSplitDAO = null;
		  FabProcessSplitForm  fabProcessSplitForm = null;

		try{

			fabProcessSplitDAO = new FabProcessSplitDAO();
			fabProcessSplitForm=fabProcessSplitDAO.getFabTrackId(productId);

			if(fabProcessSplitForm==null){
			status="Fab";
			}

			else {

				SortProcessSplitDAO sortProcessSplitDAO = new  SortProcessSplitDAO();
				String checkSortTrackId="";
				checkSortTrackId = sortProcessSplitDAO.checkSortTrackId(fabProcessSplitForm.getFabTrackId());
				if(checkSortTrackId.equals(""))
					status="Sort";
						else{
								AssemblyProcessSplitForm assemblyProcessSplitForm = null;
								AssemblyProcessSplitDAO assyProcessSplitDAO = new AssemblyProcessSplitDAO();
								assemblyProcessSplitForm = assyProcessSplitDAO.getAssemblyTrackId(fabProcessSplitForm.getFabTrackId());
								if(assemblyProcessSplitForm==null)
									status="Assembly";
									else{
											TestProcessSplitDAO testProcessSplitDAO = new  TestProcessSplitDAO();
											String checkTestTrackId="";
											checkTestTrackId = testProcessSplitDAO.checkTestTrackId(assemblyProcessSplitForm.getAssemblyTrackId());
											if(checkTestTrackId.equals(""))
												status="Test";

												else
												status = "Completed";



										}
							}
					}


			}

		catch(Exception e){
			e.printStackTrace();
			throw new ScalarException("getStageOneStatus ", e);
		   }

			return status;


}

    // added by hareesh on 15th July 2003
    public String getProductId(String baseProductName) throws ScalarException{
        return getProductId(null, baseProductName);
    }

    // changed by hareesh on 15th July 2003 // added dbTransaction
	public static String getProductId(DBTransaction dbTransaction, String baseProductName) throws ScalarException
	{
          Connection conn  = null;
		  ResultSet rs     = null;
		  String productId ="";
		  PreparedStatement pstmt = null;
          boolean status = false;
      try{

				//System.out.println(" In get Product Id base Product name "+baseProductName);

		 // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }
         //conn = DataSource.getConnection();

  		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.selectProductId"));
		 pstmt.setString(1,baseProductName);
         rs = pstmt.executeQuery();
         status = true;
		while(rs.next())
		{
		  productId = rs.getString(1);

		}

		}catch(SQLException sql){
			sql.printStackTrace();
			throw new ScalarException("In getting Base Product Id ", sql);
		   }

		   finally {
            // Commit/Rollback here it selef -- if its not in a manual transaction
                if (dbTransaction == null) {
                    try {
                        if (conn != null) {
                            if (status == true) {
                                conn.commit();
                            }
                            else {
                                conn.rollback();
                            }
                        }
                    }
                    catch (SQLException ignore) {}
                }
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

			return productId;
}

		// Get Parent Product Id
			public static String getParentProductId(String processStepId) throws ScalarException
			{
				Connection conn  = null;
				ResultSet rs     = null;
				String productId ="";
				PreparedStatement pstmt = null;

			try{

				conn = DataSource.getConnection();
  				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.selectParentProductId"));
				pstmt.setString(1,processStepId);
				rs = pstmt.executeQuery();
				while(rs.next())
				{
				productId = rs.getString(1);

				}

			}catch(SQLException sql){
				sql.printStackTrace();
				throw new ScalarException("In getting Base Product Id ", sql);
			   }

			   finally {
				if (rs != null) {
					try { rs.close(); } catch (SQLException ignore) {}
				}
				if (pstmt != null) {
					try { pstmt.close(); } catch (SQLException ignore) {}
				}
				if (conn != null) {
					try { conn.close(); } catch (SQLException ignore) {}
				}
			}

				return productId;
			}



		//Ends here

	public static String getBaseProductName(String productId) throws ScalarException
	{
          Connection conn  = null;
		  ResultSet rs     = null;
		  String baseProductName ="";
		  PreparedStatement pstmt = null;

      try{

  		 conn = DataSource.getConnection();
		 pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.selectBaseProductName"));
		 pstmt.setString(1,productId);
         rs = pstmt.executeQuery();
		while(rs.next())
		{
		  baseProductName = rs.getString(1);

		}

		}catch(SQLException sql){
		sql.printStackTrace();
		throw new ScalarException("In getting Base Product Name ", sql);
		}

			finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
			}


			//System.out.println(" In get Product Id  "+productId);

			return baseProductName;
}




    public String saveBaseProduct(String baseProductName, String comments) throws ScalarException{

	Connection conn  = null;
	ResultSet rs     = null;
	String productId =getProductId (baseProductName);
	PreparedStatement pstmt = null;

	if(productId==null || productId.equals(""))
	{
		try
		{
			conn = DataSource.getConnection();
  		   	pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.insertProduct"));

            pstmt.setString(1,baseProductName);
			pstmt.setString(2,comments);
			pstmt.executeUpdate();
		    conn.commit();
			productId = getProductId (baseProductName);



		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
			throw new ScalarException("In Saving Base Product ", sql);
		}
		finally
		{
            if (rs != null) {
                try { rs.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
		}

		return productId;
    }
	else
	{
		return "exists";
	}
}



/*Bhaskar added this method to display/populate the manufacturing part numbers
for the Data Entry purpose. Method is added on 30 july 2003*/
    public static ArrayList getInventoryManufacturingPartNumber(String type) throws ScalarException {
        Connection conn = null;
        ResultSet rs    = null;
        PreparedStatement pstmt=null;
        ArrayList partNumberList = null;
        try
        {
            partNumberList = new ArrayList();
            conn = DataSource.getConnection();
            if(type.equals("finished%"))
            pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.inventoryManufacturingPartNumber"));
            rs =  pstmt.executeQuery();
            PartNumber partNumber = new PartNumber();
            while(rs.next())
            {
                partNumber = new PartNumber();
                partNumber.setPartNumber(rs.getString(1));
                partNumber.setProcessStepId(rs.getString(2));
                partNumberList.add(partNumber);
            }
        }
        catch(Exception sql)
        {
            sql.printStackTrace();
            throw new ScalarException ("Could not get Inventory Part Number ",sql);
        }

        finally
        {
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return partNumberList;
    }


    public static void main (String[] args) throws Exception {
        //getForwardProcessStepInfoByPartNumber ("MSP2000-CB-A1");
        // getForwardProcessStepTypeNamesPartNumber("MSP2000-CB-A1");
		insertProductIfNotExisting( "ROHAN", "ROHAN" );
    }

	/**
	 * @param partNumbers
	 */
	public static String checkDupInProductMapTable(String[] partNumbers, int processStepId, String processType)throws ScalarException
	{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt = null;
		StringBuffer buff = new StringBuffer();
		String result = "";

		for( int i=0; i < partNumbers.length; i++ )
		{
			buff.append("'"+partNumbers[i].replaceAll("'","''")+"',");
		}

		try
		{
			conn = DataSource.getConnection();
			if( processStepId == -1)
			{
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.checkDupNamesFromFabMap")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
				pstmt.setString( 1 , processType );
			}
			else
			{
				pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.checkDupNamesFromFabMapWithProcessStepID")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
				pstmt.setString( 1 , processType );
				pstmt.setInt( 2 , processStepId );
			}

			rs =  pstmt.executeQuery();

			while( rs.next() )
			{
				result = result+rs.getString("Part_Number")+",";
			}
			if( result.length() < 1	)
			{
				rs.close();
				pstmt.close();
				pstmt = null;
				if( processStepId == -1)
				{
					pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.checkDupNamesFromProduct")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
					pstmt.setString( 1 , processType );
				}
				else
				{
					pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.checkDupNamesFromProductWithProcessStepID")+"("+ buff.toString().substring( 0 , buff.length()-1) +")");
					pstmt.setString( 1 , processType );
					pstmt.setInt( 2 , processStepId );
				}
				rs =  pstmt.executeQuery();
				while( rs.next() )
				{
					result = result+rs.getString("Part_Number")+",";
				}
			}
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not get check Duplicate Fab Part Numbers from database", sqle);
		}
		finally
		{
		   if (rs != null) {
			   try { rs.close(); } catch (SQLException ignore) {}
		   }
		   if (pstmt != null) {
			   try { pstmt.close(); } catch (SQLException ignore) {}
		   }
		   if (conn != null) {
			   try { conn.close(); } catch (SQLException ignore) {}
		   }
		}
		if( result.length() > 0 )
		{
			return result.substring( 0 , result.length()-1 );
		}
		else
		{
			return result;
		}
	}

	/**
		* @param processStepId
		*
		*/
	   public static void saveProductMap(DBTransaction dbTransaction,int processStepId, String[] partNumbers, ArrayList vendorLocationList)throws ScalarException
	   {
		   Connection conn = null;
		   PreparedStatement pstmt = null;
		   boolean status = false;
		   VendorLocation vendorLocation = null;
		   try
		   {
			   if (dbTransaction == null)
			   {
				   conn = DataSource.getConnection();
			   }
			   else
			   {
				   conn = dbTransaction.getConnection();
			   }

			   pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.deleteFromProductMap"));
			   pstmt.setInt(1,processStepId);
			   pstmt.executeUpdate();
			   pstmt.close();
			   pstmt = null;

			   pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.insertProductMap"));

			   for (int y=0 ; y < vendorLocationList.size() ; y++)
			   {
				   for( int i=0 ; i < partNumbers.length ; i++ )
				   {
				   		if( partNumbers[i].length() > 0 )
				   		{
				   			vendorLocation = (VendorLocation)vendorLocationList.get(y);
					   		pstmt.setInt(1,processStepId);
					   		pstmt.setString(2,partNumbers[i]);
					   		pstmt.setInt(3,vendorLocation.getLocationIDAsInt());
					   		pstmt.addBatch();
				   		}
				   }
			   }
			   pstmt.executeBatch();
			   pstmt.close();
			   pstmt=null;
			   status = true;
		   }
		   catch(SQLException sql)
		   {
			   sql.printStackTrace();
			   throw new ScalarException(" Save saveProductMap Failed", sql);
		   }
		   finally
		   {
		   // Commit/Rollback here it selef -- if its not in a manual transaction
			   if (dbTransaction == null) {
				   try {
					   if (conn != null) {
						   if (status == true) {
							   conn.commit();
						   }
						   else {
							   conn.rollback();
						   }
					   }
				   }
				   catch (SQLException ignore) {}
			   }

			   if(pstmt != null){
				   try{ pstmt.close(); }catch(Exception e){}
			   }
			   if(conn != null){
				   try{ conn.close(); }catch(Exception e){}
			   }
		   }
	   }

	/**
	 * @param processStepId
	 * @return DupFab names
	 */
	public static String[] loadProductDupNames(int processStepId) throws ScalarException
	{
		Connection conn = null;
		ResultSet rs    = null;
		PreparedStatement pstmt = null;
		StringBuffer buff = new StringBuffer();

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.selectDupNamesFromProductMap"));

			pstmt.setInt(1,processStepId);
			rs =  pstmt.executeQuery();

			while( rs.next() )
			{
				buff.append( rs.getString("Part_Number")+";") ;
			}
		}
		catch (Exception sqle)
		{
			sqle.printStackTrace();
			throw new ScalarException ("Could not get Part numbers from database", sqle);
		}
		finally
		{
		   if (rs != null) {
			   try { rs.close(); } catch (SQLException ignore) {}
		   }
		   if (pstmt != null) {
			   try { pstmt.close(); } catch (SQLException ignore) {}
		   }
		   if (conn != null) {
			   try { conn.close(); } catch (SQLException ignore) {}
		   }
		}
		if( buff.length() > 0 )
			return StringUtils.toStringArray(buff.toString().substring(0,buff.toString().length()-1),";");
		else
			return new String[0];
	}

	public static String getProductId ( DBTransaction dbTransaction, String partNumber, String processType, boolean checkMarketingPartNum ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String productId ="";
		PreparedStatement pstmt = null;
		boolean status = false;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			//String sqlQry = SqlQueryMappings.get ("SELECT P.PRODUCT_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.ProcessStep_Id = PS.ProcessStep_Id and PS.PS_Type = ? AND P.Part_Number = ?");
			//System.out.println( sqlQry );
			pstmt = conn.prepareStatement( "SELECT P.PRODUCT_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.ProcessStep_Id = PS.ProcessStep_Id and PS.PS_Type = ? AND P.Part_Number = ?" );
			pstmt.setString ( 1, processType );
			pstmt.setString ( 2, partNumber );
			rs = pstmt.executeQuery();
			status = true;
			while(rs.next())
			{
				productId = rs.getString(1);
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException("In getting Base Product Id ", sql);
		}
		finally
		{
			// Commit or Rollback here it self -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (status == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
        }
		return productId;
	}



	public static String getProductId ( DBTransaction dbTransaction, String baseProductName, boolean checkMarketingPartNum ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String productId ="";
		PreparedStatement pstmt = null;
		boolean status = false;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			String sqlQry = SqlQueryMappings.get ("operations.process.product.selectProductId");
			sqlQry += " or p_marketing_part_number = ?";
			pstmt = conn.prepareStatement( sqlQry );
			pstmt.setString ( 1, baseProductName );
			pstmt.setString ( 2, baseProductName );
			rs = pstmt.executeQuery();
			status = true;
			while(rs.next())
			{
				productId = rs.getString(1);
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException("In getting Base Product Id ", sql);
		}
		finally
		{
			// Commit or Rollback here it self -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (status == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
        }
		return productId;
	}



    /*
	Added by Bhaskar on 17 November 2003. This function avoids the
	duplication of the marketing partnumbers also when a finished good is
	being created or existing one is being updated. Called in
	FinishedGoodsAction.java.
	*/
	public static String getProcessStepId ( DBTransaction dbTransaction, String mktPartNumber ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String processStepId ="";
		PreparedStatement pstmt = null;
		boolean status = false;
		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			String sqlQry = SqlQueryMappings.get ("operations.process.product.finishedGoods.marketingPartNumber");
			pstmt = conn.prepareStatement( sqlQry );
			pstmt.setString ( 1, mktPartNumber );
			rs = pstmt.executeQuery();
			status = true;
			while(rs.next())
			{
				processStepId = ""+rs.getInt(1);
			}
		}
		catch ( SQLException sql )
		{
			sql.printStackTrace();
			//throw new ScalarException("In getting Base Product Id ", sql);
		}

		finally
		{
			// Commit or Rollback here it self -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (status == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if (rs != null) {
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null) {
				try { conn.close(); } catch (SQLException ignore) {}
			}
        }
		return processStepId;
	}


	/*
	This method is written by Bhaskar on 1 April 2004 inorder to fetch the part numbers for a given marketing
	partnumber that is given in a random way. All the matching part numbers are returned in an ArrayList.
	*/
	public static ArrayList getMatchingPartNumbers ( String partNumber ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		ArrayList partNumbers =null;
		PreparedStatement pstmt = null;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement(SqlQueryMappings.get ("operations.process.product.getMatchingPartNumbers"));

			pstmt.setString( 1, partNumber.trim()+"%" );
			rs = pstmt.executeQuery();
			partNumbers = new ArrayList();
			while(rs.next())
			{
				partNumbers.add( rs.getString("part_number"));
			}
		}
		catch(SQLException sql)
		{
			throw new ScalarException("Error In getting Part Numbers ", sql);
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return partNumbers;
	}



	/*
	This method is added by Bhaskar on 5 May 2004 inorder to get the device name from the database given a specific process step id.
	This is currently being used in Assembly Release screen. If making any change please check the code in AssemblyReleaseAction.java
	file too.
	*/
	public static String getDeviceName( int processStepId ) throws ScalarException
	{
		Connection conn  = null;
		ResultSet rs     = null;
		String deviceName ="";
		PreparedStatement pstmt = null;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.process.product.getDeviceName" ) );
			pstmt.setInt( 1, processStepId );
			rs = pstmt.executeQuery();
			while( rs.next() )
			{
				deviceName = rs.getString(1);
			}
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
			throw new ScalarException( "Problem in getting Device Name" );
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
		return deviceName;
	}

	/**
	 * @param mfgPartNumber , description
	 * @param description
     * @return productId
	 **/

	public static int insertProductIfNotExisting( String mfgPartNumber, String description ) throws ScalarException
	{
		int productId = -1;
		int processStepId = -1;
		Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt = null;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( "Select Product_ID from Product, Process_Step where Product.ProcessStep_id = Process_Step.ProcessStep_id and Part_Number = ? and PS_Type_Name = ?" );
			pstmt.setString( 1, mfgPartNumber );
			pstmt.setString( 2, Constants.LOCATION_TYPE_FINISHED_GOODS );
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				productId = rs.getInt( 1 );
			}

			if( productId == -1 )
			{
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				pstmt = conn.prepareStatement( "Insert into Process_Step ( PS_Type, PS_Type_Name, PS_Comments, TimeStamp ) values ( ?, ?, ?, CURRENT_TIMESTAMP ) " );

				pstmt.setString( 1, Constants.LOCATION_TYPE_FINISHED_GOODS );
				pstmt.setString( 2, Constants.LOCATION_TYPE_FINISHED_GOODS );
				pstmt.setString( 3, description );

				pstmt.executeUpdate();

				pstmt.close();
				pstmt = null;
				pstmt = conn.prepareStatement( "Select @@identity" );
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					processStepId = rs.getInt( 1 );
				}

				pstmt.close();
				pstmt = null;
				pstmt = conn.prepareStatement( "Insert into Product ( Part_Number, ProcessStep_Id, P_Marketing_Part_Number, TimeStamp, Is_Approved ) values ( ?, ?, ?, CURRENT_TIMESTAMP, ? ) " );

				pstmt.setString( 1, mfgPartNumber );
				pstmt.setInt( 2, processStepId );
				pstmt.setString( 3, mfgPartNumber );
				pstmt.setInt( 4, 0 );

				pstmt.executeUpdate();

				pstmt.close();
				pstmt = null;
				pstmt = conn.prepareStatement( "Select @@identity" );
				rs = pstmt.executeQuery();
				if( rs.next() )
				{
					productId = rs.getInt( 1 );
				}
			}
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
			throw new ScalarException( " Could not insert new product " );
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null)
			{
				try
				{
					conn.commit();
					conn.close();
				} catch (SQLException ignore) {}
			}
		}
		return productId;
	}

    /**
     *
     */
    public static void syncFinishedGoodsToEpicore( String userName ) throws ScalarException
    {
		Connection conn  = null;
		ResultSet rs     = null;
		PreparedStatement pstmt = null;
		ArrayList chiptracFGList 	= new ArrayList();
		ArrayList chiptracPartList 	= new ArrayList();
		FinishedGoodsForm finshedGoods  = null;
        ArrayList loc_in_Epicore = new ArrayList (10);   //holds all the locations in the EpicoreDB
        String acct_code = "";
        String zone_code = "";
        boolean success = false;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( "select Part_Number, P_Marketing_Part_Number, P_Comments, Is_Approved, Product.ProcessStep_ID from Product, Process_Step where Product.ProcessStep_ID = Process_Step.ProcessStep_ID and PS_Type_Name = ?" );
			pstmt.setString( 1, Constants.LOCATION_TYPE_FINISHED_GOODS );
			rs = pstmt.executeQuery();
			while ( rs.next() )
			{
				finshedGoods = new FinishedGoodsForm();
				finshedGoods.setProductName( rs.getString( "Part_Number" ) );
				finshedGoods.setMktPartNumber( rs.getString( "P_Marketing_Part_Number" ) );
				finshedGoods.setComments( rs.getString( "P_Comments" ) );
				finshedGoods.setIsApproved( rs.getBoolean( "Is_Approved" ) ? false : true );
				finshedGoods.setProcessStepId( rs.getInt( "ProcessStep_ID" ) );

				if( !chiptracPartList.contains( finshedGoods.getProductName() ) )
				{
					chiptracPartList.add( finshedGoods.getProductName() );
					chiptracFGList.add( finshedGoods );
				}
			}

            //Retriving the Vendor Address Locations.
            Map locationDetails = new HashMap ();      //a map that contains loc (City/Vendor Name) as key and VendorAddress as value
            VendorAddress vendorAddress;               //for forming Vendor Address
            Map process_id_locations = new HashMap (); //a map that contaings process id as key and string that contains comma separated locs as value
            ArrayList locations_for_process_id;        //holds the locations for a process id
            String temp = "";                          //for forming a string, containing comma separated locations for a process id

            if( chiptracFGList != null && chiptracFGList.isEmpty() == false )
			{
                if ( rs != null )
                    rs.close();
                rs = null;
                if ( pstmt != null )
                    pstmt.close();
                pstmt = null;

                for( int i = 0; i < chiptracFGList.size(); i++ )
                {
                    finshedGoods  = (FinishedGoodsForm) chiptracFGList.get( i );
                    locations_for_process_id = new ArrayList ();
                    temp = "";

                    if ( finshedGoods != null )
                    {
                        pstmt = conn.prepareStatement( "select L_Vendor_Name, Addr.Address_ID Address_ID, Address_1, Address_2, " +
                                " City, State, Zipcode, Country, Phone_Number, Fax_Number, Email, Contact_Person, Cell_Number, " +
                                " Address_3, Address_4, Epicore_Ship_Code from Process_Step_Location_Table, Location Loc, Address Addr " +
                                " where Process_Step_Location_Table.Location_Id = Loc.Location_Id and Loc.Address_Id = Addr.Address_Id and processStep_Id = ?" );
                        pstmt.setInt( 1, finshedGoods.getProcessStepId() );
                        rs = pstmt.executeQuery();
                        while ( rs.next() )
                        {
                            vendorAddress = new VendorAddress ();
                            vendorAddress.setVendorName( rs.getString ("L_Vendor_Name") );
                            vendorAddress.setAddressId( rs.getInt ("Address_ID") );
                            vendorAddress.setAddress1( rs.getString ("Address_1") );
                            vendorAddress.setAddress2( rs.getString ("Address_2") );
                            vendorAddress.setCity( rs.getString ("City") );
                            vendorAddress.setState( rs.getString ("State") );
                            vendorAddress.setZipCode( rs.getString ("Zipcode") );
                            vendorAddress.setCountry( rs.getString ("Country") );
                            vendorAddress.setPhoneNumber( rs.getString ("Phone_Number") );
                            vendorAddress.setFaxNumber( rs.getString("Fax_Number") );
                            vendorAddress.setEmail( rs.getString ("Email") );
                            vendorAddress.setContactPerson( rs.getString ("Contact_Person") );
                            vendorAddress.setCellNumber( rs.getString ("Cell_Number") );
                            vendorAddress.setAddress3( rs.getString ("Address_3") );
                            vendorAddress.setAddress4( rs.getString ("Address_4") );
                            vendorAddress.setEpicoreShipCode( rs.getString ("Epicore_Ship_Code") );

                            if ( vendorAddress.getCity() != null && vendorAddress.getCity().trim().length() > 0 )
                            {
                                locations_for_process_id.add( vendorAddress.getCity().trim() );
                            }
                            else if ( vendorAddress.getVendorName() != null && vendorAddress.getVendorName().trim().length() > 0 )
                            {
                                locations_for_process_id.add( vendorAddress.getVendorName().trim() );
                            }

                            if( !( locationDetails.containsKey( vendorAddress.getVendorName().trim() ) || locationDetails.containsKey( vendorAddress.getCity().trim() ) ) )
                            {
                                if ( vendorAddress.getCity() != null && vendorAddress.getCity().trim().length() > 0 )
                                {
                                    locationDetails.put ( vendorAddress.getCity().trim(), vendorAddress );
                                }
                                else if ( vendorAddress.getVendorName() != null && vendorAddress.getVendorName().trim().length() > 0 )
                                {
                                    locationDetails.put ( vendorAddress.getVendorName(), vendorAddress);
                                }
                            }//if
                        }//while

                        //forming the a string that contaings comma separated locations for a process id
                        //and putting processid as key and that string as value in  process_id_locations map
                        for( int j = 0; j < locations_for_process_id.size() ; j++ )
                        {
                            temp = temp + locations_for_process_id.get( j ) + ";";
                        }
                        if ( temp.trim().length() > 0 && temp.endsWith(";") )
                        {
                            temp = temp.substring( 0, temp.length() -1 );
                        }

                        process_id_locations.put(  new Integer (finshedGoods.getProcessStepId() ), temp );
                    }//if
                }//for

                if ( rs != null )
                    rs.close();
                rs = null;
                if ( pstmt != null )
                    pstmt.close();
                pstmt = null;
                if ( conn != null )
                    conn.close();
                conn = null;

                conn = DataSource.getConnection( DATASOURCE_TYPE_GREAT_PLAINS );

                //Retriving all the Locations in the Epicore DB and forming a array list
                //A location is inserted, only if it is not exists in the Epicore DB.
                pstmt = conn.prepareStatement( "SELECT location FROM Locations" );
                rs = pstmt.executeQuery();
                while ( rs.next() )
                {
                    loc_in_Epicore.add( rs.getString("location") );
                }

                if ( rs != null )
                    rs.close();
                rs = null;
                if ( pstmt != null )
                    pstmt.close();
                pstmt = null;

                //Retriving the acct_code and zone_code form Epicore DB, used while inserting the locations into DB.
                /*pstmt = conn.prepareStatement( "SELECT  acct_code FROM in_account");
                rs = pstmt.executeQuery();
                if ( rs.next() )
                {
                    acct_code = rs.getString( "acct_code" );
                }

                if ( rs != null )
                    rs.close();
                rs = null;
                if ( pstmt != null )
                    pstmt.close();
                pstmt = null;
				*/

				acct_code = "CHIPS";
                pstmt = conn.prepareStatement( "exec fs_generic_find @sqltext = 'SELECT zone_code FROM arzone WHERE zone_code >= '''' ORDER BY zone_code, zone_desc'");
                rs = pstmt.executeQuery();
                if ( rs.next() )
                {
                    zone_code = rs.getString( "zone_code" );
                }

                if ( rs != null )
                    rs.close();
                rs = null;
                if ( pstmt != null )
                    pstmt.close();
                pstmt = null;

                //Inserting the Vendor Address Locations in the Epicore DB
                pstmt = conn.prepareStatement( "INSERT INTO locations ( location, name, addr1, addr2, " +
                        " addr3, addr4, addr5, addr_sort1, addr_sort2, addr_sort3, phone, contact_name, " +
                        " consign_customer_code, consign_vendor_code, aracct_code, zone_code, location_type, " +
                        " apacct_code, dflt_recv_bin ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );

                Set set = locationDetails.keySet();
                Iterator it = set.iterator();
                boolean execBatch = false;
                while ( it.hasNext() )
                {
                    String location =  (String) it.next ();
                    vendorAddress = ( VendorAddress ) locationDetails.get( location );

                    //Inserting the Location into EpicoreDB, if not is exists in the EpicoreDB
                    if ( !( loc_in_Epicore.contains( location.length() > 10 ? location.substring(0, 10) : location ) ) )
                    {
                        execBatch = true;
                        pstmt.setString( 1, location.length() > 10 ? location.substring(0, 10) : location );
                        pstmt.setString( 2, location.length() > 30 ? location.substring(0, 30) : location );

                        if ( vendorAddress.getAddress1() != null )
                        {
                            pstmt.setString( 3, vendorAddress.getAddress1().length() > 40 ? vendorAddress.getAddress1().substring(0, 40) : vendorAddress.getAddress1() );
                        }
                        else
                        {
                            pstmt.setString( 3, "" );
                        }

                        if ( vendorAddress.getAddress2() != null )
                        {
                            pstmt.setString( 4, vendorAddress.getAddress2().length() > 40 ? vendorAddress.getAddress2().substring(0, 40) : vendorAddress.getAddress2() );
                        }
                        else
                        {
                            pstmt.setString( 4, "" );
                        }

                        if ( vendorAddress.getAddress3() != null )
                        {
                            pstmt.setString( 5, vendorAddress.getAddress3().length() > 40 ? vendorAddress.getAddress3().substring(0, 40) : vendorAddress.getAddress3() );
                        }
                        else
                        {
                            pstmt.setString( 5, "" );
                        }

                        if ( vendorAddress.getAddress4() != null )
                        {
                            pstmt.setString( 6, vendorAddress.getAddress4().length() > 40 ? vendorAddress.getAddress4().substring(0, 40) : vendorAddress.getAddress4() );
                        }
                        else
                        {
                            pstmt.setString( 6, "" );
                        }
                        pstmt.setString( 7, "" );
                        pstmt.setString( 8, "" );
                        pstmt.setString( 9, "" );
                        pstmt.setString( 10, "" );
                        if ( vendorAddress.getPhoneNumber() != null )
                        {
                            pstmt.setString( 11, vendorAddress.getPhoneNumber().length() > 30 ? vendorAddress.getPhoneNumber().substring(0, 30) : vendorAddress.getPhoneNumber() );
                        }
                        else
                        {
                            pstmt.setString( 11, "" );
                        }

                        if ( vendorAddress.getContactPerson() != null )
                        {
                            pstmt.setString( 12, vendorAddress.getContactPerson().length() > 30 ? vendorAddress.getContactPerson().substring(0, 30) : vendorAddress.getContactPerson() );
                        }
                        else
                        {
                            pstmt.setString( 12, "" );
                        }
                        pstmt.setString( 13, "" );
                        pstmt.setString( 14, "" );
                        pstmt.setString( 15, acct_code );
                        pstmt.setString( 16, zone_code );
                        pstmt.setInt( 17, 0 );
                        pstmt.setString( 18, acct_code );
						pstmt.setString( 19, "" );
                        pstmt.addBatch();
                    }//if
				}//while
                if( execBatch )
                {
        		    pstmt.executeBatch();
                }
            }//if
            //upto here

			if( chiptracFGList != null && chiptracFGList.isEmpty() == false )
			{
				if ( rs != null )
                    rs.close();
				rs = null;
                if ( pstmt != null )
				    pstmt.close();
				pstmt = null;

				pstmt = conn.prepareStatement( "{ call sp_ct_to_epi_update_chiptrac_products_to_epicore ( ?, ?, ?, ?, ?, ?, ? ) }" );
				for( int i = 0; i < chiptracFGList.size(); i++ )
				{
					finshedGoods = (FinishedGoodsForm) chiptracFGList.get( i );
                    if ( finshedGoods.getProductName() != null )
                    {
					    pstmt.setString( 1, finshedGoods.getProductName().trim().length() > 30 ? finshedGoods.getProductName().trim().substring( 0, 30) : finshedGoods.getProductName().trim() );
                    }
                    else
                    {
                        pstmt.setString( 1, "");
                    }

                    if ( finshedGoods.getMktPartNumber() != null )
                    {
					    pstmt.setString( 2, finshedGoods.getMktPartNumber().trim().length() > 30 ? finshedGoods.getMktPartNumber().trim().substring( 0, 30) : finshedGoods.getMktPartNumber().trim() );
                    }
                    else
                    {
                        pstmt.setString( 2, "" );
                    }

                    if ( finshedGoods.getComments() != null )
                    {
					    pstmt.setString( 3, finshedGoods.getComments().trim().length() > 255 ? finshedGoods.getComments().trim().substring( 0, 255) : finshedGoods.getComments().trim() );
                    }
                    else
                    {
                        pstmt.setString( 3, "" );
                    }

                    if ( userName != null )
                    {
					    pstmt.setString( 4, userName.trim().length() > 20 ? userName.substring( 0, 20) : userName.trim() );
                    }
                    else
                    {
                        pstmt.setString( 4, "" );
                    }
					pstmt.setInt   ( 5, finshedGoods.getIsApproved() ? 1 : 0 );
                    pstmt.setString( 6, (String) process_id_locations.get( new Integer ( finshedGoods.getProcessStepId() ) ) );
                    pstmt.setString( 7, acct_code );

					//System.out.println( finshedGoods.getProductName() );
					pstmt.addBatch();
				}
				pstmt.executeBatch();

				/*pstmt = conn.prepareStatement( "select part_no from inv_master" );
				rs = pstmt.executeQuery();
				while ( rs.next() )
				{
					epicoreFGList.add( rs.getString( "part_no" ) );
				}

				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

				chiptracProductList.addAll( chiptracFGList );

				// Remove the already existing parts in Epicore
				chiptracFGListIterator = chiptracFGList.iterator();

				while( chiptracFGListIterator.hasNext() )
				{
					finshedGoods = ( FinishedGoodsForm )chiptracFGListIterator.next();
					String part_number = finshedGoods.getProductName();
					if( epicoreFGList.contains( part_number ) )
					{
						chiptracProductList.remove( finshedGoods );
					}
				}

				//pstmt = conn.prepareStatement( "insert into inv_master ( part_no, sku_no, description, category, type_code, cubic_feet, weight_ea, labor, uom, entered_who, allow_fractions, lb_tracking  ) values ( ?, ?, ?, 'FINISHED', 'FINISHED', 0.0, 0.0, 0.0, 'EA', ?, 0, 'Y' )" );
				pstmt = conn.prepareStatement( "{ call sp_update_chiptrac_products_to_epicore ( ?, ?, ?, ?, ? ) }" );
				for( int i = 0; i < chiptracProductList.size(); i++ )
				{
					finshedGoods = (FinishedGoodsForm) chiptracProductList.get( i );
					pstmt.setString( 1, finshedGoods.getProductName() );
					pstmt.setString( 2, finshedGoods.getMktPartNumber() );
					pstmt.setString( 3, finshedGoods.getComments() );
					pstmt.setString( 4, userName );
					pstmt.setInt( 5, finshedGoods.getIsApproved() ? 1 : 0 );

					pstmt.addBatch();
				}
				pstmt.executeBatch();*/
			}
            success = true;
		}
		catch( SQLException sql )
		{
			sql.printStackTrace();
			throw new ScalarException( " Could not insert new product " );
		}
		finally
		{
			if ( rs != null )
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if ( pstmt != null )
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if ( conn != null )
			{
				try
				{
                    if ( success )
                    {
					    conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
					conn.close();
				} catch (SQLException ignore) {}
			}
		}
    }

    //Added by Rohan on June 4th 2004
    /**
     * This method will return the product ID of the given Mfg. Part Number
     *
     * @param dbTransaction
     * @param mfgPartNumber
     * @return
     */
    public static int getProductIDForMfgPartNumber( DBTransaction dbTransaction, String mfgPartNumber ) throws ScalarException
    {
        int productID = -1;

        Connection conn         = null;
        PreparedStatement pstmt = null;
		ResultSet rs            = null;

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( "select product_id from product where Part_number = ? and P_Marketing_Part_Number is not null " );

			pstmt.setString( 1, mfgPartNumber );
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				productID = rs.getInt( "product_id" );
			}
		}
		catch(SQLException sql)
		{
			throw new ScalarException( "Exception while retreiving the product ID", sql);
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

        return productID;
    }

    //Added by Rohan on June 15th 2004
    /**
     * This method will return a HashMap with Alternate Part Number as keys
     * and PartNumbers as values pertaining to the given Vendor and Process Type
     *
     * @param vendorName
     * @param processType
     * @return
     * @throws ScalarException
     */
    public static HashMap getAlternatePartNumbersMap( String vendorName, String processType ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
		ResultSet rs            = null;
        HashMap alternatePartNumbersMap = new HashMap();

		try
		{
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.process.product.alternatePartNumber" ) );
            pstmt.setString( 1, processType );
            pstmt.setString( 2, vendorName );

			rs = pstmt.executeQuery();

			while(rs.next())
			{
                alternatePartNumbersMap.put( rs.getString( "Alternate_Part_Number" ), rs.getString( "Part_Number" ) );
			}
		}
		catch(SQLException sql)
		{
			throw new ScalarException( "Exception while constructing the alternate part numbers map", sql);
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

        return alternatePartNumbersMap;
    }


    public static HashMap getPreviousStageProductNames ( String basePartNumber, String testStage ) throws ScalarException {

         // this method totally specific PXW client. and proudct T03B1250*********************

        // Requirement given the Base product name, get all the finsihsed goods products,
        // and for each finished proudct fetch the corresponding Test (QA smaple product names)
        // for pxw for the base proudct T03B1250,each FG are make from only one test process name.


        Connection conn         = null;
        PreparedStatement pstmt = null;
		ResultSet rs            = null;
        HashMap previousStageProductNames = new HashMap();
        HashMap tempHashMap = new HashMap();

		try
		{
            /*select product.Part_Number, product.processStep_id from product, process_step
            where product.processStep_id = process_step.processStep_id
            and process_Step.PS_Type_name = 'Finished Goods'
            and p_parent_product_ID =
            (select product_ID from product where part_number = 'T03B1250' and p_parent_product_id is null)

            select product_name from fn_get_process_flow_by_processstep_id (8138) where ps_type = 'QA Sample' */

			conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (" select product.Part_Number AS Part_Number, product.processStep_id AS ProcessStep_id from product, process_step "+
                                           " where product.processStep_id = process_step.processStep_id "+
                                           " and process_Step.PS_Type_name = 'Finished Goods' "+
                                           " and p_parent_product_ID = "+
                                           " (select product_ID from product where part_number = ? " +
                                           " and p_parent_product_id is null)" );

            pstmt.setString( 1 , basePartNumber);

			rs = pstmt.executeQuery();

			while(rs.next())
			{
                tempHashMap.put( rs.getString( "ProcessStep_id" ), rs.getString( "Part_Number" )  );
                //System.out.println("... "+  rs.getString( "ProcessStep_id" ) + "...."+ rs.getString( "Part_Number" ) );
			}

            rs.close();
            pstmt.close();

            String processStep_id = null;
            Set processStepIdSet =  tempHashMap.keySet();
            Iterator iterrator =  processStepIdSet.iterator();
            String fgPartNumber = null;

            while ( iterrator.hasNext() ){

                fgPartNumber = null;

                processStep_id  = (String) iterrator.next();
                fgPartNumber = ( String ) tempHashMap.get(processStep_id);

                pstmt = conn.prepareStatement ( "select product_name from fn_get_process_flow_by_processstep_id ( ?  ) " +
                                                " where ps_type = ? " );

                pstmt.setString( 1 , processStep_id );
                pstmt.setString( 2 , testStage);

			    rs = pstmt.executeQuery();


			    if (rs.next())
			    {
                    previousStageProductNames.put( fgPartNumber, rs.getString( "product_name" )  );
                    //System.out.println("... "+  fgPartNumber + "...."+ rs.getString( "product_name" ) );
			    }

                rs.close();
                pstmt.close();
            }


		}
		catch(SQLException sql)
		{
			throw new ScalarException( "Exception while constructing the alternate part numbers map", sql);
		}

		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}

        return previousStageProductNames;

    }

    public static ArrayList loadAllBaseProducts ( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            ps = conn.prepareStatement ( SqlQueryMappings.get ("operations.process.product.getAllBaseProductsList") );
            rs = ps.executeQuery();
            //Here we are getting the Product Id instead of ProcessStepIds because the for the Base products their will be no ProcesStep Ids.
            //Because we are getting the Product ids , sp_wip_info_report proc is modified to support the Product ids.
            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("IST_Manufacturing_Part_Number"), rs.getString("Product_ID") ) );
                //System.out.println(" Part_Number :  " + rs.getString("IST_Manufacturing_Part_Number") + "  :  " + rs.getString("Product_ID")  );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all base products information " , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }
    
    /*
     * Returns all the Chip type Base Products and their product id.
     */
    public static ArrayList loadChipBaseProducts ( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            ps = conn.prepareStatement ( SqlQueryMappings.get ("operations.process.product.loadChipPartNumbers") );
            rs = ps.executeQuery();
            //Here we are getting the Product Id instead of ProcessStepIds because the for the Base products their will be no ProcesStep Ids.
            //Because we are getting the Product ids , sp_wip_info_report proc is modified to support the Product ids.
            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("Part_Number"), rs.getString("Product_ID") ) );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all base products information " , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }
    
    /*
	 * To return part Numbers based on the base product select and processtype
	 * 
	 */
    public static ArrayList getPartNumberByBaseProductAndPsType(  String psTypeName, int productId ) throws ScalarException
    {
    	ArrayList productList = new ArrayList();
		Connection conn 	= null;
		ResultSet rs 		= null;
		PreparedStatement ps = null;
		PartNumber partNumber 		= null;
		
		 try
	        {
	            conn = DataSource.getConnection();
	            ps = conn.prepareStatement ("select distinct part_Number ,p.processstep_id from product p,process_step ps where p.processstep_id = ps.processstep_id and ps.ps_type_name = ? and p.p_parent_product_id =?");
	            ps.setString( 1, psTypeName );
	            ps.setInt( 2, productId );
	            rs = ps.executeQuery();

	            while( rs.next() )
		    	{
			    	partNumber = new PartNumber();
				    partNumber.setPartNumber( rs.getString(1) );
					partNumber.setProcessStepId( rs.getString(2) );
					
					productList.add( partNumber );
				}
	        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all products information " , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return productList;
    }

    public static ArrayList getPartNumberByBaseProductAndPsTypeList( String ps_Type, int productId ) throws ScalarException
    {
    	ArrayList productList = new ArrayList();
		Connection conn 	= null;
		ResultSet rs 		= null;
		PreparedStatement ps = null;

		try
        {
            conn = DataSource.getConnection();

            ps = conn.prepareStatement ("select distinct part_Number ,p.processstep_id from product p,process_step ps where p.processstep_id = ps.processstep_id and ps.ps_type = ? and p.p_parent_product_id =?");
            ps.setString( 1, ps_Type );
            ps.setInt( 2, productId );

            rs = ps.executeQuery();

            while( rs.next() )
            {
                productList.add( new LabelValueBean( rs.getString("part_Number"), rs.getString("processstep_id")) );
            }
        }
        catch ( SQLException se )
        {
            se.printStackTrace();
            throw new ScalarException ("Could not load all products By PS Type information " , se);
        }

        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null)
            {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return productList;
    }
    
    public static ArrayList getPartNumberByBaseProductAndPsType( String ps_Type, int productId, boolean includeBoardsAndKitsChildProducts ) throws ScalarException
    {
       ArrayList productList = new ArrayList();
       Connection conn 	= null;
       ResultSet rs 		= null;
       PreparedStatement ps = null;
       PartNumber partNumber 		= null;

       try
       {
           conn = DataSource.getConnection();
           if( includeBoardsAndKitsChildProducts )
           {
           		if( productId == 0 || productId == -1 )
           		{
           			ps = conn.prepareStatement ("SELECT DISTINCT PART_NUMBER, P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_Name = ? AND ( P_TYPE IS NULL OR P_TYPE = 'BOARD' OR P_TYPE = 'KIT' )");
           		}
           		else
           		{
           			ps = conn.prepareStatement ("SELECT DISTINCT PART_NUMBER, P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_Name = ? AND P.P_PARENT_PRODUCT_ID = ? AND ( P_TYPE IS NULL OR P_TYPE = 'BOARD' OR P_TYPE = 'KIT' )");
           		}
           }
           else
           {
           		if( productId == 0 || productId == -1 )
           		{
           			ps = conn.prepareStatement ("SELECT DISTINCT PART_NUMBER, P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_Name = ? AND P_TYPE IS NULL");
           		}
           		else
           		{
           			ps = conn.prepareStatement ("SELECT DISTINCT PART_NUMBER, P.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_Name = ? AND P.P_PARENT_PRODUCT_ID = ? AND P_TYPE IS NULL");
           		}
           }
           
           ps.setString( 1, ps_Type );
           if( productId != 0 && productId != -1 )
           {
           		ps.setInt( 2, productId );
           }
           rs = ps.executeQuery();

           while( rs.next() )
           {
               partNumber = new PartNumber();
               partNumber.setPartNumber( rs.getString( "PART_NUMBER" ) );
               partNumber.setProcessStepId( rs.getString( "PROCESSSTEP_ID" ) );

               productList.add( partNumber );
           }
       }
       catch ( SQLException se )
       {
           se.printStackTrace();
           throw new ScalarException ("Could not load all products information " , se);
       }

       finally
       {
           if (rs != null) {
               try { rs.close(); } catch (SQLException igonore) { }
           }

           if (ps != null) {
               try { ps.close(); } catch (SQLException igonore) { }
           }

           if (conn != null) {
               try { conn.close(); } catch (SQLException igonore) { }
           }
       }

       	return productList;
    }
    
    public static ArrayList loadAllFinishedGoods() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();

        try {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("operations.process.product.getAllFGProductsList"));

           rs = pstmt.executeQuery();

           FinishedGoods finishedGoods = null;
           while(rs.next()) {
        	finishedGoods = new FinishedGoods(
                            rs.getInt("ProcessStep_ID"),
                            rs.getString("IST_Manufacturing_Part_Number"),
                            rs.getString("IST_Marketing_Part_Number"),
							rs.getInt("Product_ID"),
							rs.getString("P_Type"),
                            rs.getString("P_Comments"));

                productList.add(finishedGoods);
           }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return productList;
    }

    public static int getProcessStepIdByMfgPartNumber( String mfgPartNumber ) throws ScalarException
    {
    	int processStepId 	= -1;
		Connection conn 	= null;
		ResultSet rs 		= null;
		PreparedStatement ps = null;

		try
		{
			conn 	= DataSource.getConnection();
			ps 		= conn.prepareStatement( "select process_step.ProcessStep_Id from product, process_step where product.processStep_id = process_step.processStep_id and part_number= ? and PS_Type_Name = 'Finished Goods'" );
			ps.setString( 1, mfgPartNumber );

			rs = ps.executeQuery();

			if( rs.next() )
			{
				processStepId = rs.getInt( "ProcessStep_Id" );
			}

		}
		catch ( SQLException se )
		{
			se.printStackTrace();
			throw new ScalarException ("Could not fetch the Process Step Id for the product "+ mfgPartNumber, se );
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException igonore) { }
			}

			if (ps != null) {
				try { ps.close(); } catch (SQLException igonore) { }
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException igonore) { }
			}
		}
    	return processStepId;
    }

	public static int getShippingProcessStepIdByMfgPartNumber( String mfgPartNumber ) throws ScalarException
	{
		int processStepId 	= -1;
		Connection conn 	= null;
		ResultSet rs 		= null;
		CallableStatement cs = null;
		
		try
		{
			conn 	= DataSource.getConnection();
			cs 		= conn.prepareCall( "SELECT dbo.fn_getShipping_fgProcessStep_id_mfgPartNumber( ? )" );
			cs.setString( 1, mfgPartNumber );

			rs = cs.executeQuery();

			if( rs.next() )
			{
				processStepId = rs.getInt( 1 );
			}
		}
		catch ( SQLException se )
		{
			se.printStackTrace();
			throw new ScalarException ("Could not fetch the Process Step Id for the product "+ mfgPartNumber, se );
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException igonore) { }
			}

			if (cs != null) {
				try { cs.close(); } catch (SQLException igonore) { }
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException igonore) { }
			}
		}
		return processStepId;
	}

	/**
	 * @param processStep_id
	 * @return
	 */
	public static ArrayList fetchPreviousProcesses( int processStep_id )throws ScalarException
	{
		ArrayList processList 	= new ArrayList();
		Connection conn 		= null;
		ResultSet rs 			= null;
		CallableStatement cs 	= null;
		Product product 		= null;

		try
		{
			conn 	= DataSource.getConnection();
			cs 		= conn.prepareCall( "SELECT * FROM fn_get_process_flow_by_processstep_id( ? )" );
			cs.setInt( 1, processStep_id );

			rs = cs.executeQuery();

			while( rs.next() )
			{
				product = new Product();
				product.setProcessStepId( rs.getInt( "ProcessStep_ID" ) );
				product.setProcessType( rs.getString( "PS_Type" ) );
				product.setBaseProcessType( rs.getString( "Process_Type_Name" ) );
				product.setPartNumber( rs.getString( "Product_name" ) );

				processList.add( product );
			}
		}
		catch( Exception e )
		{
			throw new ScalarException( "Could not fetch the previous Processes ", null );
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException igonore) { }
			}

			if (cs != null) {
				try { cs.close(); } catch (SQLException igonore) { }
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException igonore) { }
			}
		}
		return processList;
	}

	/**
	 * @param processType
	 * @param locationId
     * @param processStepId
	 * This method is used in Lot Change Action to fetch all the products in that Location
	 * It removes the selected process step id
	 */
	public static ArrayList getProductsByLocation( String processType, String locationId, String processStepId ) throws ScalarException
	{
        ArrayList productList 	= new ArrayList();
		Connection conn 		= null;
		ResultSet rs 			= null;
		PreparedStatement pstmt	= null;
		String tempProcessStepId = null;

		try
		{
            conn 	= DataSource.getConnection();

            if( "BOARD".equalsIgnoreCase( processType ) || "KIT".equalsIgnoreCase( processType ) )
            {
                pstmt 	= conn.prepareStatement( SqlQueryMappings.get( "operations.process.product.getAllBoardOrKitProductsList" ) );
                pstmt.setInt( 1, Integer.parseInt( locationId ) );
                pstmt.setString( 2, processType );
            }
            else
            {
                pstmt 	= conn.prepareStatement ( SqlQueryMappings.get( "operations.process.product.getAllChipProductsList" ) );
                pstmt.setInt( 1, Integer.parseInt( locationId ) );
                pstmt.setString( 2, processType );
            }

			rs = pstmt.executeQuery();

			while( rs.next() )
			{
                tempProcessStepId = rs.getString( "PROCESSSTEP_ID" );

                if( "BOARD".equalsIgnoreCase( processType ) || "KIT".equalsIgnoreCase( processType ) )
                {
					if( !tempProcessStepId.equalsIgnoreCase( processStepId ) )
					{
						productList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), tempProcessStepId ) );
					}
                }
                else
                {
                    if( !tempProcessStepId.equalsIgnoreCase( processStepId ) )
                    {
                        if( processType.equalsIgnoreCase( "TEST" ) || processType.equalsIgnoreCase( "SORT" ) )
                        {
                            productList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ) + "/" + rs.getString( "PS_TYPE" ), tempProcessStepId ) );
                        }
                        else
                        {
                            productList.add( new LabelValueBean( rs.getString( "PART_NUMBER" ), tempProcessStepId ) );
                        }
                    }
                }
			}
		}
		catch( Exception e )
		{
			throw new ScalarException( "Could not fetch the products ", null );
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException igonore) { }
			}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException igonore) { }
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException igonore) { }
			}
		}
		return productList;
	}

	public static String getProductNameByProcessStep_Id( int processStep_Id ) throws ScalarException
	{
		String part_number = null;
		Connection conn 		= null;
		ResultSet rs 			= null;
		PreparedStatement pstmt	= null;

		try
		{
			conn 	= DataSource.getConnection();
			pstmt 	= conn.prepareStatement ( "SELECT Part_Number from product where ProcessStep_Id = ?" );
			pstmt.setInt( 1, processStep_Id );

			rs = pstmt.executeQuery();

			if( rs.next() )
			{
				part_number = rs.getString( "Part_Number" );
			}
		}
		catch( Exception e )
		{
			throw new ScalarException( "Could not fetch the part number ", null );
		}
		finally
		{
			if (rs != null) {
				try { rs.close(); } catch (SQLException igonore) { }
			}

			if (pstmt != null) {
				try { pstmt.close(); } catch (SQLException igonore) { }
			}

			if (conn != null) {
				try { conn.close(); } catch (SQLException igonore) { }
			}
		}

		return part_number;
	}

    public static ArrayList loadAllFGProductsByBaseProductId( int productId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList productList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement ("SELECT Process_Step.ProcessStep_ID, Product.Part_Number, Product.P_Marketing_part_Number, Product.Product_ID, Product.P_Type, Product.P_Comments FROM product,Process_Step  where Process_Step.PS_type_name like 'Finished Goods' and product.p_parent_product_id = ? and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number");
            pstmt.setInt( 1, productId );
            rs = pstmt.executeQuery();

            FinishedGoods finishedGoods = null;
            while( rs.next() )
            {
                finishedGoods = new FinishedGoods(
                rs.getInt   ("ProcessStep_ID"),
                rs.getString("Part_Number"),
                rs.getString("P_Marketing_Part_Number"),
                rs.getInt   ("Product_ID"),
                rs.getString("P_Type"),
                rs.getString("P_Comments") );

                productList.add( finishedGoods );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load Finished Goods Info from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return productList;
    }
    public static ArrayList getAllManufacturingProductsByType( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList products = new ArrayList();
        LabelValueBean labelValueBean = null;
        boolean success = false;

        /*labelValueBean = new LabelValueBean ( "---Select Product---","0");
        products.add ( labelValueBean );*/
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( SqlQueryMappings.get( "operations.product.getProducts" ) );
            rset = pstmt.executeQuery();

            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "part_number" ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }
            pstmt.close();
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.product.getBoardsOrKitsOrBoms" ) );

            //to load BOARDS
            pstmt.setString ( 1, "BOARD" );
            labelValueBean = new LabelValueBean ( "---BOARD---","0");
            products.add( labelValueBean );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "part_number" ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }


            //to load KITS
            pstmt.setString ( 1, "KIT" );
            labelValueBean = new LabelValueBean ( "---KIT---","0");
            products.add( labelValueBean );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "part_number" ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }

            //to load BOMS, NO Need to laod BOM Part Numbers.
			/*
            pstmt.setString ( 1, "BOM" );
            labelValueBean = new LabelValueBean ( "---BOM---","0");
            products.add( labelValueBean );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                labelValueBean = new LabelValueBean( rset.getString ( "part_number" ), rset.getString ( "PRODUCT_ID") );
                products.add( labelValueBean );
            }*/
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            System.out.println ( "----Exception while getting all the products-----");
            throw new ScalarException ( "Could Not Get All the Products" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch ( SQLException ignore ){ }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException e ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException e ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException e ) { }
            }
        }
        return products;
    }

    public static int getProcessStepIdByProductId( int product_Id ) throws ScalarException
    {
    	int processStepId 		= -1;
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        try
        {
        	conn = DataSource.getConnection();
        	pstmt = conn.prepareStatement( "select processStep_Id from product where product_Id = ?" );
        	pstmt.setInt( 1, product_Id );
        	rset = pstmt.executeQuery();

        	if( rset.next() )
        	{
        		processStepId = rset.getInt( "processStep_Id" );
			}

        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not fetch the processStep Id for the product" );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException e ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException e ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException e ) { }
            }
        }
        return processStepId;
    }

    public static ArrayList getBlankFGIforFG( int processStep_Id ) throws ScalarException
    {
    	ArrayList fgiList 		= new ArrayList();
    	Product product 		= null;
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        try
        {
        	conn = DataSource.getConnection();
        	pstmt = conn.prepareStatement( "select p.part_number, p.processStep_Id, p.P_Comments from FG_TO_FGI_MAP_TABLE FTFMT, Product P where FTFMT.FGI_Process_Step_Id = P.ProcessStep_Id and FTFMT.FG_Process_Step_Id = ? ORDER BY SUBSTRING(PART_NUMBER, 10, 2) DESC, SUBSTRING(PART_NUMBER, 1, 5) " );
        	pstmt.setInt( 1, processStep_Id );
        	rset = pstmt.executeQuery();

        	while( rset.next() )
        	{
        		product = new Product();
        		product.setPartNumber( rset.getString( "part_number" ) );
        		product.setProcessStepId( rset.getInt( "processStep_Id" ) );
        		product.setDescription( rset.getString( "P_comments" ) );
        		fgiList.add( product );
			}
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not fetch the processStep Id for the product" );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException e ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException e ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException e ) { }
            }
        }
        return fgiList;
    }

    public static Product getFGInfo( int processStep_Id ) throws ScalarException
    {
    	Product product 		= null;
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        try
        {
        	conn = DataSource.getConnection();
        	pstmt = conn.prepareStatement( "select p.part_number, p.processStep_Id, p.P_Comments, p.P_ProgramCode, p.P_Algorithm, p.P_CRC_Code from Product P where ProcessStep_Id = ?" );
        	pstmt.setInt( 1, processStep_Id );
        	rset = pstmt.executeQuery();

        	if( rset.next() )
        	{
        		product = new Product();
        		product.setPartNumber( rset.getString( "part_number" ) );
        		product.setProcessStepId( rset.getInt( "processStep_Id" ) );
        		product.setDescription( rset.getString( "P_comments" ) );
        		product.setProgramCode( rset.getString( "P_ProgramCode" ) );
        		product.setAlgorithm( rset.getString( "P_Algorithm" ) );
        		product.setCrcCode( rset.getString( "P_CRC_Code" ) );
			}

        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not fetch the processStep Id for the product" );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException e ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException e ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException e ) { }
            }
        }
        return product;
    }

    public static ArrayList loadAllPartNumberByBaseProductIdAndProcessType( int productId, String processType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList partNoList = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement ("SELECT Process_Step.ProcessStep_ID, Product.Part_Number FROM product,Process_Step  where Process_Step.PS_type_name like ? and product.p_parent_product_id = ? and product.processStep_id=Process_Step.ProcessStep_ID order by Product.Part_Number");
            pstmt.setString( 1, processType );
            pstmt.setInt( 2, productId );
            rs = pstmt.executeQuery();

            while( rs.next() )
            {
                partNoList.add( new org.apache.struts.util.LabelValueBean ( rs.getString("Part_Number"), rs.getString("ProcessStep_ID") )  );
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load part numbers from database", sqlex);
        }
        finally
        {
            if (rs != null)
            {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        return partNoList;
    }

    public static ArrayList loadAllPartsForProcessType (String processType) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            ps = conn.prepareStatement ( "SELECT P.PROCESSSTEP_ID, PART_NUMBER FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE_NAME = ? ORDER BY PART_NUMBER" );
            ps.setString(1, processType);
            rs = ps.executeQuery();
            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("PART_NUMBER"), rs.getString("PROCESSSTEP_ID") ) );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all part numbers for Process Type" , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }

    public static ArrayList loadAllPartsForProcessTypeByPsType (String processType) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            ps = conn.prepareStatement ( "SELECT P.PROCESSSTEP_ID, PART_NUMBER, PS_TYPE FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = ? ORDER BY PART_NUMBER" );
            ps.setString(1, processType);
            rs = ps.executeQuery();

            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("PART_NUMBER") , rs.getString("PROCESSSTEP_ID") ) );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all part numbers for Process Type By PS Type" , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }
     public static ArrayList loadAllPartsForProcessTypeNotInOtherByPsType (String processType, String excludepsType) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try
        {
            conn = DataSource.getConnection();

            ps = conn.prepareStatement ( "SELECT P.PROCESSSTEP_ID, PART_NUMBER, PS_TYPE FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = ?  AND Part_Number  NOt IN ( SELECT PART_NUMBER FROM PRODUCT P, PROCESS_STEP PS WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PS_TYPE = ?  )  ORDER BY PART_NUMBER" );
            ps.setString(1, processType);
            ps.setString(2, excludepsType);
            rs = ps.executeQuery();

            while( rs.next() )
            {
                list.add ( new org.apache.struts.util.LabelValueBean ( rs.getString("PART_NUMBER") , rs.getString("PROCESSSTEP_ID") ) );
            }

        }
         catch ( SQLException se ) {
            se.printStackTrace();
            throw new ScalarException ("Could not load all part numbers for Process Type By PS Type" , se);
        }

        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException igonore) { }
            }

            if (ps != null) {
                try { ps.close(); } catch (SQLException igonore) { }
            }

            if (conn != null) {
                try { conn.close(); } catch (SQLException igonore) { }
            }
        }

        return list;
    }
    public static ArrayList getAllProcessTypeNames ()
    {
        ArrayList processTypeNames = new ArrayList();

        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "FAB", "FAB" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "WAFER STORE", "Wafer Store" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "SORT", "SORT" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "DIE BANK", "Die Bank" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "ASSEMBLY", "ASSEMBLY" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "TEST", "TEST" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "CLASS STORE", "Class Store" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "MARK&PACK", "Mark and Pack" ) );
        processTypeNames.add( new org.apache.struts.util.LabelValueBean( "FINISHED GOODS", "Finished Goods" ) );

        return processTypeNames;
    }

    /**
     * To get Only Base Products(Chips) list. The list will not include 'Software','Boards' and 'Kits'.
     * @param dbTransaction
     * @return
     * @throws ScalarException
     */

    public static ArrayList getBaseProductsWithoutBoardKits( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset  = null;
        ArrayList baseProductList = new ArrayList();
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT PART_NUMBER, PRODUCT_ID FROM PRODUCT WHERE P_Parent_Product_ID IS NULL AND P_TYPE IS NULL ORDER BY PART_NUMBER" );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                baseProductList.add ( new LabelValueBean ( rset.getString("PART_NUMBER"), rset.getString("PRODUCT_ID") ) );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Base Products", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return baseProductList;
    }


	public static ArrayList getAllProductsByType( ) throws ScalarException
    {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ArrayList allDieProductList = new ArrayList();

		try {
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement("SELECT DISTINCT PART_NUMBER, PS_Type_Name_Order_No  FROM ( select  Part_Number, " +
                    "                               CASE WHEN PS_Type='FAB' THEN 1 \n" +
                    "                                     WHEN PS_Type='Wafer Bank' THEN 2 \n" +
                    "                                     WHEN PS_Type='Fab Option' THEN 2 \n" +
                    "                                     WHEN PS_Type='Die Bank' THEN 2\n" +
                    "                                     WHEN PS_Type='Assembly' THEN 3 \n" +
                    "                                     WHEN PS_Type='FT' THEN 3 \n" +
                    "                                     WHEN PS_Type='FPO' THEN 3\n" +
                    "                                     WHEN PS_Type='Tape & Reel' THEN 3 \n" +
                    "                                     WHEN PS_Type='FG' THEN 3 \n" +
                    "                                     ELSE 4\n" +
                    "                               END AS PS_Type_Name_Order_No From product p , process_step PS where P.processstep_id = PS.processstep_id AND P_parent_Product_id IS NOT NULL\n" +
                    " AND P_type is Null ) as TEMP_TABLE order by PS_Type_Name_Order_No, Part_Number ");
           
            rs = pstmt.executeQuery();

			while (rs.next())
            {
				allDieProductList.add( new LabelValueBean ( rs.getString ( "PART_NUMBER" ), rs.getString( "PART_NUMBER" ) ) );

			}
		} catch (SQLException sql) {
			sql.printStackTrace();
			throw new ScalarException("Could not Load All Products  ", sql);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignore) {
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException ignore) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ignore) {
				}
			}
		}

		return allDieProductList;
	}


	public static ArrayList getBaseProductsByPartNumber( String partNumber ) throws ScalarException
    {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ArrayList baseProductList = new ArrayList();

		try
        {
			conn = DataSource.getConnection();
			pstmt = conn.prepareStatement("Select Product_ID, Part_Number, Obsolete from product where Product_id in (select distinct P_parent_Product_id from product where part_number = ? )" );
            pstmt.setString(1, partNumber );
            rs = pstmt.executeQuery();

			while (rs.next())
            {
                BaseProduct baseProduct = new BaseProduct();
                baseProduct.setProductId( rs.getString("Product_ID"));
                baseProduct.setProductName(rs.getString("Part_Number"));
				baseProduct.setObsolete(rs.getInt("Obsolete") == 0 ? true : false);

                baseProductList.add( baseProduct );
            }
		}
        catch (SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException("Could not Load selected part number Base Products ", sql);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignore) {
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException ignore) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ignore) {
				}
			}
		}

		return baseProductList;
	}

	/**
	 * Obsoletes the Product Family.
	 *
	 * @param baseProductId
	 * @param obsolete
	 * @throws ScalarException
	 */
	public static void updateProductFamilyStatus( DBTransaction dbTransaction, int baseProductId, int obsolete, String userName) throws ScalarException
    {
		Connection conn         = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
        ResultSet rs = null;
        boolean success         = false;

        try
        {
			if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            if ( obsolete > 0)
            {
                pstmt = conn.prepareStatement("Select DISTINCT Product_id,Part_Status from PRODUCT_HISTORY where CONVERT(VARCHAR,TIMESTAMP, 101) = (SELECT CONVERT(VARCHAR,MAX(TIMESTAMP), 101) FROM PRODUCT_HISTORY WHERE Is_BaseProduct_Obsolete = 0 AND (Product_id = ? OR P_Parent_Product_id = ?) ) ");

                pstmt.setInt(1, baseProductId);
                pstmt.setInt(2, baseProductId);
                rs = pstmt.executeQuery();

                //UPDATE PRODUCT SET Obsolete = ?, Part_Status = ? WHERE PRODUCT_ID = ? OR P_PARENT_PRODUCT_ID = ?
                pstmt1 = conn.prepareStatement(SqlQueryMappings.get("operations.process.product.updateProductObsolete"));

                while (rs.next())
                {
                    pstmt1.setInt(1, obsolete);
                    pstmt1.setString(2, rs.getString("Part_Status"));
                    pstmt1.setInt(3, rs.getInt("Product_id") );
                    pstmt1.setInt(4, rs.getInt("Product_id"));

                    pstmt1.addBatch();

                }
                pstmt1.executeBatch();

                pstmt1.close();
                pstmt1 = null;
                rs.close();
                rs= null;
                pstmt.close();
                pstmt = null;                
            }
            else
            {
                pstmt = conn.prepareStatement("DELETE FROM PRODUCT_HISTORY WHERE CONVERT(VARCHAR,TIMESTAMP, 101) = CONVERT(VARCHAR, Current_Timestamp, 101) AND Is_BaseProduct_Obsolete = 0 AND (Product_id = ? OR P_Parent_Product_id = ?) ");
                pstmt.setInt(1, baseProductId);
                pstmt.setInt(2, baseProductId);

                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement("INSERT INTO PRODUCT_HISTORY Select Product_id,Part_Number, P_Parent_Product_id, P_Marketing_Part_Number, P_Comments, Is_Approved, Part_Status, Product_Group, Is_Primary_FG, 0,1,"+"'" + userName +"'"+", Current_Timestamp from Product where Product_id = ? OR P_Parent_Product_id = ? ");
                pstmt.setInt(1, baseProductId);
                pstmt.setInt(2, baseProductId);

                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;

                pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.product.updateProductObsolete"));
                pstmt.setInt(1, obsolete);
                pstmt.setString(2, "Obsolete");
                pstmt.setInt(3, baseProductId);
                pstmt.setInt(4, baseProductId);

                pstmt.executeUpdate();
                pstmt.close();
                pstmt = null;
            }
            success = true;
        }
        catch (Exception sql)
        {
			sql.printStackTrace();
			throw new ScalarException("Could not update Product Obsolete for baseProductId: "+ baseProductId, sql);
		}
        finally
        {    // Commit/Rollback here it selef -- if its not in a manual transaction
               if( dbTransaction == null )
               {
                   try
                   {
                       if( conn != null )
                       {
                           if( success == true )
                           {
                               conn.commit();
                           }
                           else
                           {
                               conn.rollback();
                           }
                       }
                   }
                   catch( SQLException ignore )
                   {
                   }
               }
            if (rs != null)
            {
				try
                {
					rs.close();
				}
                catch (SQLException se)
                {
				}
			}
            if (pstmt != null)
            {
				try
                {
					pstmt.close();
				}
                catch (SQLException se)
                {
				}
			}
			if (conn != null)
            {
				try
                {
					conn.close();
				}
                catch (SQLException se)
                {
				}
			}
		}
	}
   /* public static ArrayList getPartNumberBySpecificProcess(String type)
			throws ScalarException {

		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		ArrayList partNumberList = null;
		try {
			partNumberList = new ArrayList();
			conn = DataSource.getConnection();

			pstmt = conn.prepareStatement(SqlQueryMappings
					.get("operations.process.getPartNumberBySpecificProcess"));
			pstmt.setString(1, type);

			rs = pstmt.executeQuery();

			PartNumber partNumber = null;

			while (rs.next()) {
				partNumber = new PartNumber();
                partNumber.setProcessStepId(rs.getInt("ProcessStep_ID") +"");
                partNumber.setProcessType(rs.getString("PS_Type"));
                partNumber.setProcessTypeName(rs.getString("PS_Type_Name"));
                partNumber.setPartNumber(rs.getString("Part_Number") );
                partNumber.setProductId(rs.getString("Product_ID") );

                partNumberList.add(partNumber);
			}

		} catch (Exception sql) {
			sql.printStackTrace();
			throw new ScalarException("Could not get Part Number by type", sql);
		}
        finally {
            if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ignore) {
				}
			}
            if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException ignore) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException ignore) {
				}
			}
		}

		return partNumberList;
	}*/
}